package com.zijie.shopviewapi.view.dao;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;
import java.util.Map;

@Mapper
public interface ViewMapper {

    @Select("SELECT g.`name`,count(0) AS value FROM order_item oi LEFT JOIN good g ON g.id=oi.good_id GROUP BY good_id ORDER BY COUNT(0) DESC limit 10")
    List<Map<String,Object>> hotItems();

    @Select("SELECT count(0) FROM `order` ")
    Long countOrders();

    @Select("select count(0) from member where enabled = 1")
    Long countMembers();

    @Select("select sum(total_pay) from `order`")
    Double countPrices();

    @Select("SELECT DATE(pay_time) `date`,count(0) count FROM `order` where pay_time between #{startDate} and #{endDate} GROUP BY DATE(pay_time)")
    List<Map<String,Object>> orderCountGroupByDate(Date startDate, Date endDate);

    @Select("SELECT DATE(pay_time) `date`,SUM(total_pay) count FROM `order` where pay_time between #{startDate} and #{endDate} GROUP BY DATE(pay_time)")
    List<Map<String,Object>> priceCountGroupByDate(Date startDate, Date endDate);
}
